//-----------------------------------------------------------------------
// <copyright file="SQLiteDatabase.cs" company="NextLine Software">
//     Copyright (c) NextLine Software. All rights reserved.
// </copyright>
//-----------------------------------------------------------------------

namespace NextLine.Data.SQLite
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.IO;
    using System.IO.IsolatedStorage;
    using System.Linq;
    using System.Text;
    using System.Xml.Linq;

    /// <summary>
    /// Contains functions for updating databases.
    /// </summary>
    public class SQLiteDatabase
    {
        /// <summary>
        /// The version of a database when the version information is missing.
        /// </summary>
        private const double UnknownVersion = -1;

        /// <summary>
        /// Clears all the data in the databases specified in the NextLine Database xml file.
        /// </summary>
        /// <param name="databaseXml">String containing the xml for the database.</param>
        public void ClearDatabases(string databaseXml)
        {
            XElement xmlDoc;
            using (TextReader reader = new StringReader(databaseXml))
            {
                xmlDoc = XElement.Load(reader);
            }

            // loop through all the databases and update each database individually
            var databasesQuery =
                from d in xmlDoc.Elements("Database")
                select d;

            foreach (XElement databaseNode in databasesQuery)
            {
                this.ClearDatabase(databaseNode);
            }
        }

        /// <summary>
        /// Updates or creates the databases specified in the NextLine Database xml file.
        /// </summary>
        /// <param name="databaseXml">String containing the xml for the database.</param>
        public void UpdateDatabases(string databaseXml)
        {
            XElement xmlDoc;
            using (TextReader reader = new StringReader(databaseXml))
            {
                xmlDoc = XElement.Load(reader);
            }

            // loop through all the databases and update each database individually
            var databasesQuery =
                from d in xmlDoc.Elements("Database")
                select d;

            foreach (XElement databaseNode in databasesQuery)
            {
                this.UpdateDatabase(databaseNode);
            }
        }

        /// <summary>
        /// Updates or creates a database based on the NextLine Database xml schema.
        /// </summary>
        /// <param name="databaseNode">Node containing the xml for the database.</param>
        private void ClearDatabase(XElement databaseNode)
        {
            // reference to our connection
            IDbConnection connection = null;
            try
            {
                // the database location to create the database
                string databasePath = databaseNode.Attribute("DatabaseName").Value;

                // create the connection to the database
                // we specify the New field based on the existance of the file
                connection = new SQLiteConnection(databasePath);
                connection.Open();

                this.DropVersionTable(connection);

                // loop through all the tables and update them individually
                foreach (var tableNode in databaseNode.Elements("Table"))
                {
                    this.DropTable(connection, tableNode);
                }
            }
            finally
            {
                // make sure the connection is closed
                if (connection != null && connection.State != System.Data.ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
        }

        /// <summary>
        /// Updates or creates a database based on the NextLine Database xml schema.
        /// </summary>
        /// <param name="databaseNode">Node containing the xml for the database.</param>
        private void UpdateDatabase(XElement databaseNode)
        {
            // reference to our connection
            IDbConnection connection = null;
            try
            {
                // the database location to create the database
                string databasePath = databaseNode.Attribute("DatabaseName").Value;

                // create the connection to the database
                // we specify the New field based on the existance of the file
                IsolatedStorageFile store = IsolatedStorageFile.GetUserStoreForApplication();
                bool newDatabase = !store.FileExists(databasePath);
                connection = new SQLiteConnection(databasePath);
                connection.Open();

                // retrieve the database version
                double databaseVersion = 1;
                try
                {
                    databaseVersion = double.Parse(databaseNode.Attribute("Version").Value);
                }
                catch
                {
                }

                // see if we have to create the version table.
                // if not, then check the version
                if (newDatabase)
                {
                    this.CreateVersionTable(connection, databaseVersion);
                }
                else
                {
                    // determine if the version is OK
                    double currentVersion = this.GetDatabaseVersion(connection);
                    if (currentVersion == UnknownVersion)
                    {
                        this.CreateVersionTable(connection, databaseVersion);
                    }
                    else if (currentVersion == databaseVersion)
                    {
                        return;
                    }
                    else if (currentVersion > databaseVersion)
                    {
                        throw new Exception("Invalidate database version");
                    }
                }

                // loop through all the tables and update them individually
                foreach (var tableNode in databaseNode.Elements("Table"))
                {
                    this.UpdateTable(connection, tableNode);
                }
            }
            finally
            {
                // make sure the connection is closed
                if (connection != null && connection.State != System.Data.ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
        }

        /// <summary>
        /// Retrieves the version of the database.
        /// </summary>
        /// <param name="connection">An open connection to the database.</param>
        /// <returns>The version of the database or -1 i the version information is missing.</returns>
        private double GetDatabaseVersion(IDbConnection connection)
        {
            using (var cmd = connection.CreateCommand())
            {
                cmd.CommandText = "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='DatabaseInfo'";
                if ((long)cmd.ExecuteScalar() == 0)
                {
                    return UnknownVersion;
                }
            }

            using (var cmd = connection.CreateCommand())
            {
                cmd.CommandText = "SELECT Version FROM DatabaseInfo";
                return (double)cmd.ExecuteScalar();
            }
        }

        /// <summary>
        /// Creates a table to store database information such as the Database Version.
        /// </summary>
        /// <param name="connection">The database connection.</param>
        private void DropVersionTable(IDbConnection connection)
        {
            if (this.TableExists(connection, "DatabaseInfo"))
            {
                using (var cmd = connection.CreateCommand())
                {
                    cmd.CommandText = "DROP TABLE DatabaseInfo";
                    cmd.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// Creates a table to store database information such as the Database Version.
        /// </summary>
        /// <param name="connection">The database connection.</param>
        /// <param name="databaseVersion">The version to set for the database.</param>
        private void CreateVersionTable(IDbConnection connection, double databaseVersion)
        {
            using (var cmd = connection.CreateCommand())
            {
                cmd.CommandText = string.Format("CREATE TABLE DatabaseInfo (Version {0})", this.MapFieldType("Double"));
                cmd.ExecuteNonQuery();
            }

            using (var cmd = connection.CreateCommand())
            {
                cmd.CommandText = string.Format("INSERT INTO DatabaseInfo (Version) VALUES ({0})", databaseVersion);
                cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// Drops a table from the database.
        /// </summary>
        /// <param name="connection">The database connection.</param>
        /// <param name="tableNode">Node containing the xml for the table.</param>
        private void DropTable(IDbConnection connection, XElement tableNode)
        {
            string tableName = tableNode.Attribute("TableName").Value;
            if (this.TableExists(connection, tableName))
            {
                using (var cmd = connection.CreateCommand())
                {
                    cmd.CommandText = "DROP TABLE " + tableName;
                    cmd.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// Updates or creates a table based on the NextLine Database xml schema.
        /// </summary>
        /// <param name="connection">
        /// The connection to the database.
        /// This connection is already openned.
        /// </param>
        /// <param name="tableNode">Node containing the xml for the table.</param>
        private void UpdateTable(IDbConnection connection, XElement tableNode)
        {
            // set the table name
            string tableName = tableNode.Attribute("TableName").Value;

            // first we need to see if we need to create the table, or if we need to update the table
            IDbCommand cmd = connection.CreateCommand();
            cmd.CommandText = string.Format("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='{0}'", tableName);
            long cnt = (long)cmd.ExecuteScalar();
            if (cnt == 0)
            {
                // a new table
                StringBuilder sb = new StringBuilder();
                sb.AppendFormat("CREATE TABLE {0}", tableName);

                // loop through all the fields and add them to our fields list
                IList<string> fields = new List<string>();
                foreach (var fieldNode in tableNode.Elements("Field"))
                {
                    if (fieldNode.Attribute("PrimaryKey") != null && fieldNode.Attribute("PrimaryKey").Value.ToLower() == "true")
                    {
                        fields.Add(string.Format("{0} {1} PRIMARY KEY", fieldNode.Attribute("FieldName").Value, this.MapFieldType(fieldNode.Attribute("DataType").Value)));
                    }
                    else
                    {
                        fields.Add(string.Format("{0} {1}", fieldNode.Attribute("FieldName").Value, this.MapFieldType(fieldNode.Attribute("DataType").Value)));
                    }
                }

                if (fields.Count > 0)
                {
                    sb.AppendFormat(" ({0})", string.Join(", ", fields.ToArray()));
                }

                cmd.CommandText = sb.ToString();
                cmd.ExecuteNonQuery();

                // add indecies
                foreach (var index in tableNode.Elements("Index"))
                {
                    string indexName = index.Attribute("IndexName").Value;
                    if (string.IsNullOrEmpty(indexName))
                    {
                        throw new InvalidOperationException("IndexName is required for a SQLite table index for table: " + tableName);
                    }

                    IList<string> indexFields = new List<string>();
                    foreach (var indexField in index.Elements("Field"))
                    {
                        indexFields.Add(indexField.Attribute("FieldName").Value);
                    }

                    if (indexFields.Count == 0)
                    {
                        throw new InvalidOperationException("Index fields are required for a SQLite index '" + indexName + "' for table: " + tableName);
                    }
                     
                    sb = new StringBuilder();
                    sb.AppendFormat("CREATE INDEX {0} ON {1} ({2})", indexName, tableName, string.Join(", ", indexFields.ToArray()));
                    cmd.CommandText = sb.ToString();
                    cmd.ExecuteNonQuery();
                }
            }
            else
            {
                // we need to update the table, we will do this a stupid way...
                foreach (var fieldNode in tableNode.Elements("Field"))
                {
                    try
                    {
                        cmd.CommandText = string.Format("ALTER TABLE {0} ADD COLUMN {1} {2}", tableName, fieldNode.Attribute("FieldName").Value, this.MapFieldType(fieldNode.Attribute("DataType").Value));
                        cmd.ExecuteNonQuery();
                    }
                    catch
                    {
                    }
                }
            }
        }

        /// <summary>
        /// Converts a NextLine Database Schema Field Type to a SQLite field type.
        /// </summary>
        /// <param name="xmlType">The friendly name mapping</param>
        /// <returns>The SQL type mapping.</returns>
        private string MapFieldType(string xmlType)
        {
            switch (xmlType.ToLower())
            {
                case "text":
                    return "TEXT";

                case "binary":
                    return "BLOB";

                case "integer":
                    return "INTEGER";

                case "double":
                    return "REAL";
            }

            throw new Exception("Invalid field type: " + xmlType);
        }

        private bool TableExists(IDbConnection connection, string tableName)
        {
            using (IDbCommand cmd = connection.CreateCommand())
            {
                cmd.CommandText = string.Format("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='{0}'", tableName);
                return (long)cmd.ExecuteScalar() > 0;
            }
        }
    }
}
